Database SQL Analyser: Tutorial 3[Back] [SQL Test] [Database] This page shows how SQL commands can be applied to a database: |
Database contents
ID | Surname | Firstname | Age | Gender | Interest | Country |
7 | Smith | Fred | 43 | Male | Soccer | Scotland |
8 | Balley | Bert | 44 | Male | Rugby | England |
9 | Allan | Karen | 20 | Female | Tennis | USA |
10 | Carroll | Kirk | 25 | Female | Soccer | Canada |
11 | Smith | Allan | 50 | Male | Golf | Scotland |
12 | MacDonald | Iain | 22 | Male | Golf | Scotland |
13 | McAlpine | Carol | 33 | Female | Tennis | Mexico |
14 | Wilson | Mike | 33 | Male | Tennis | England |
15 | Smith | Karen | 40 | Female | Golf | USA |
16 | Buchan | Martin | 20 | Male | Football | Scotland |
Presentation
Tutorial
Which commands are used for the following:
- Add 10 marks to Test 1 for a person with the Firstname of Fred Solution
- Remove 10 marks to Test 1 for a person with the Firstname of Fred Solution
- Add 1 mark to Test 2 for a person with the Surname of Smith Solution
- Remove 1 mark to Test 2 for a person with the Surname of Smith Solution
- Insert ID=12, Surname="Noone",Firstname="Fred",FullAddress="2222 Test",Test 1=50, Test 2=50, Male, Age=40 Solution
Samples
SQL Commands used
- SELECT * FROM db1 Try
- INSERT INTO db1 VALUES (10,'Bert','Allan','1111 Test',100,100,'M',50) SELECT * FROM db1 Try
- DELETE FROM db1 WHERE (Surname='Allan') Try
- UPDATE db1 SET [Test 1]=100 WHERE Surname='Malcolm' Try
- UPDATE db1 SET [Test 1]=10,[Test 2]=30 WHERE Surname='Malcolm' Try
- UPDATE db1 SET [Test 1]=[Test 1]+10 WHERE Surname='Malcolm' Try
- CREATE TABLE myTest (FirstName char(50), Surname char(50), FullAddress char(50), [Test 1] Number, [Test 2] Number, Age Number) Try
- SELECT * FROM myTest Try
- INSERT INTO myTest VALUES ('Fred','Martin','2222 Fake Street',70,80,50) Try
- DELETE FROM db1 WHERE (Surname='Martin') Try
- DROP TABLE myTest Try